(defun sqlserver-storedprocedure(&optional name)
  (interactive)
  (or name (setq name (read-from-minibuffer "Enter Stored Procedure Name: ")))
  (insert (format "IF EXISTS (SELECT * FROM sys.objects WHERE name = '%s' AND type = 'P')
    DROP PROCEDURE dbo.%s
GO
CREATE PROCEDURE dbo.%s
AS
BEGIN transaction
BEGIN TRY
END TRY
BEGIN CATCH
SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;

ROLLBACK transaction
END CATCH
GO" name name name))
  )

(defun sqlserver-table(&optional name)
  (interactive)
  (or name (setq name (read-from-minibuffer "Enter Table Name: ")))
  (insert (format "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.%s') AND type in (N'U'))
    DROP TABLE dbo.%s
GO
CREATE TABLE %s(
   Id int identity(1,1) PRIMARY KEY,
)
GO

CONSTRAINT [PK_%s] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)
" name name name name)
          )
  )

(defun sqlserver-create-login()
  (interactive)
  (insert "-- login name and database are case sensitive.
create login [AUSYDVIRTSVR18\PacomIsTestGuest] from windows with DEFAULT_DATABASE = [Firebird]
")
  )

(defun sqlserver-database(&optional DatabaseName)
  (interactive)
  (or DatabaseName (setq DatabaseName (read-from-minibuffer "Enter Database Name: ")))
  (insert (format "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'%s')
BEGIN
        DROP DATABASE %s;
END

CREATE DATABASE %s;
GO

ALTER DATABASE [%s] SET  ENABLE_BROKER 
GO
" DatabaseName DatabaseName DatabaseName DatabaseName)
          )
  )

(defun sqlserver-database-create()
  (interactive)
  (insert "create database FirebirdDatabase on primary
(NAME = N'MH_Test', FILENAME = N'D:\\Program Files\\FirebirdDatabase.mdf')
LOG ON 
(NAME = N'MH_Test_log', FILENAME = N'D:\\Program Files\\FirebirdDatabase.ldf')


create table [Doctor]
(
    [Index] varchar(100) not null,
    [Name] varchar(100) not null,
    [Gender] varchar(5) not null,
    [Birthday] date not null,
    [Title] varchar(100) not null,
    constraint [PK_Doctor_Index] primary key clustered
    (
        Index ASC
    )
) on [primary]

create table Patient
(
    [Index] varchar(100) not null,
    [Name] varchar(100) not null,
    [Gender] varchar(5) not null,
    [Birthday] date not null,
    [ID] varchar(100) not null,
    CONSTRAINT [PK_Patient_Index] PRIMARY KEY CLUSTERED 
    (
        [Index] asc
    )
) on [primary]
 

alter table [Patient] add constraint [FK_Patient_Index] foreign key ([Patient_Index]) references [Patient]([Index])
alter table [Doctor] add constraint [FK_Doctor_Index] foreign key ([Doctor_Index]) references [Doctor]([Index])")
  )

(defun sqlserver-insert()
  (interactive)
  (insert "insert into dbo.TableName values('',1)")
  )

(defun sqlserver-import-databae()
  (interactive)
  (insert "USE [master]
GO
CREATE DATABASE [YourDB] ON
(FILENAME = N'C:\Data\yourdb.mdf')

FOR ATTACH
GO")
  )

(defun sqlserver-with()
  (interactive)
  (insert "use Test

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
	EmployeeID smallint NOT NULL,
	FirstName nvarchar(30)  NOT NULL,
	LastName  nvarchar(40) NOT NULL,
	Title nvarchar(50) NOT NULL,
	DeptID smallint NOT NULL,
	ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sanchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO")
  )

(defun sqlserver-update()
  (interactive)
  (insert "UPDATE dbo.Table2 
SET --dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2 
    INNER JOIN dbo.Table1 
    ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO")
  )

(defun sqlserver-deadlock()
  (interactive)
  (insert "sql profiler -> deadlock graph -> associated objid: 72057594073186304


use Firebird
SELECT OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM sys.partitions
WHERE partition_id = 72057594073186304;")
  )

(defun sqlserver-cmdshell()
  (interactive)
  (insert "-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

exec xp_cmdshell 'mkdir c:\\bin'
exec xp_cmdshell 'echo xxx > c:\\bin\\1.txt'

")
  )

(defun sqlserver-backup()
  "backup master database"
  (interactive)
  (insert "BACKUP DATABASE [master] TO DISK='D:\DatabaseBackup\master.bak'")
  )

(defun sqlserver-print-table()
  "sql server print"
  (interactive)
  (insert "DECLARE @name nvarchar(128)

DECLARE cur CURSOR FOR
SELECT name FROM sys.database_principals

OPEN cur

FETCH NEXT FROM cur INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN   
PRINT @name
FETCH NEXT FROM cur INTO @name;
END

CLOSE cur;
DEALLOCATE cur;")
  )

(defun mysql-grant()
  (interactive)
  (insert "SHOW GRANTS FOR 'root'@'localhost';

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

")
  )

(defun sql-upcase-word()
  (interactive)
  (setq abc '("insert"
              "into"
              "from"
              "declare"
              "values"
              "not"
              "null"
              "default"
              "int"
              "varchar"
              "create"
              "table"
              "auto"
              "increment"
              "primary"
              "key"
              "database"
              "alter"
              "set"
              "enable_broker"
              )
        )
  (save-excursion  
    (beginning-of-buffer)
    (while abc
      (setq temp (car abc))
      (setq abc (cdr abc))
      (setq temp (format "\\_<%s\\_>" temp))
      (while (re-search-forward temp nil t)
        (backward-word 1)
        (upcase-word 1)
        )
      (beginning-of-buffer)
      )
    )
  )

(defun mysql-password()
  (interactive)
  (insert "UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

mysqld_safe --init-file=/home/me/mysql-init &
")
  )

(defun mysql-password-firstTimeChange()
  "mysql admin root password"
  (interactive)
  (insert "mysqladmin -u root password 123456")
  )

(defun mysql-export()
  (interactive)
  (insert "mysqldump -u youruser -pUserPassword yourdatabase > wantedsqlfile.sql")
  )

(defun mysql-login()
  (interactive)
  (insert "mysql -u rick -p")
  )

(defun mysql-compile()
  (interactive)
  (insert "gcc -I/usr/include/mysql select3.c -L/usr/lib/mysql -lmysqlclient -lz -o select3")
  )

